#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CY.GFive.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace CY.GFive.DALProviders.SqlServerProvider
{
	public class StaffAssessProvider : CY.GFive.Core.Providers.DALProvider.IStaffAssessProvider
	{
		#region Sql
		private static readonly string SqlSelectStaffAssess = "SELECT [Id], [StaffCode],[StaffCate],[AssessCate],[AssessScore] FROM [StaffAssess] WHERE  [Id]=@Id";

		private static readonly string SqlInsertStaffAssess = "INSERT INTO [StaffAssess]([StaffCode],[StaffCate],[AssessCate],[AssessScore]) values(@StaffCode,@StaffCate,@AssessCate,@AssessScore);SELECT SCOPE_IDENTITY();";

		private static readonly string SqlUpdateStaffAssess = "UPDATE [StaffAssess] SET [StaffCode] = @StaffCode,[StaffCate] = @StaffCate,[AssessCate] = @AssessCate,[AssessScore] = @AssessScore WHERE  [Id]=@Id";

		private static readonly string SqlDeleteStaffAssess = "DELETE [StaffAssess] WHERE  [Id]=@Id";

		private static readonly string SqlGetAllStaffAssess = "SELECT [Id], [StaffCode],[StaffCate],[AssessCate],[AssessScore] FROM [StaffAssess]";
		#endregion

		#region IStaffAssessProvider Members
		public Core.Business.StaffAssess Select(int id)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, id);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectStaffAssess);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				Core.Business.StaffAssess staffAssess = new Core.Business.StaffAssess();

				if (!reader.IsDBNull(0)) staffAssess.Id = reader.GetInt32(0);
				if (!reader.IsDBNull(1)) staffAssess.StaffCode = reader.GetString(1);
				if (!reader.IsDBNull(2)) staffAssess.StaffCate = reader.GetString(2);
				if (!reader.IsDBNull(3)) staffAssess.AssessCate = reader.GetString(3);
				if (!reader.IsDBNull(4)) staffAssess.AssessScore = reader.GetDecimal(4);

				return staffAssess;
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();

				return null;
			}
		}

		public void Insert(Core.Business.StaffAssess staffAssess)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@StaffCode", SqlDbType.NVarChar, staffAssess.StaffCode);
			sql.AddParameter("@StaffCate", SqlDbType.NVarChar, staffAssess.StaffCate);
			sql.AddParameter("@AssessCate", SqlDbType.NVarChar, staffAssess.AssessCate);
			sql.AddParameter("@AssessScore", SqlDbType.Decimal, staffAssess.AssessScore);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertStaffAssess);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				if (!reader.IsDBNull(0)) staffAssess.Id = int.Parse(reader.GetDecimal(0).ToString());

				reader.Close();
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void Update(Core.Business.StaffAssess staffAssess)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@StaffCode", SqlDbType.NVarChar, staffAssess.StaffCode);
			sql.AddParameter("@StaffCate", SqlDbType.NVarChar, staffAssess.StaffCate);
			sql.AddParameter("@AssessCate", SqlDbType.NVarChar, staffAssess.AssessCate);
			sql.AddParameter("@AssessScore", SqlDbType.Decimal, staffAssess.AssessScore);
			sql.AddParameter("@Id", SqlDbType.Int, staffAssess.Id);
			sql.ExecuteSql(SqlUpdateStaffAssess);
		}

		public void Delete(Core.Business.StaffAssess staffAssess)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, staffAssess.Id);
			sql.ExecuteSql(SqlDeleteStaffAssess);
		}

		public IList<Core.Business.StaffAssess> GetAllStaffAssess()
		{
			IList<Core.Business.StaffAssess> staffAssesslist = new List<Core.Business.StaffAssess>();
			SqlServerUtility sql = new SqlServerUtility();

			SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllStaffAssess);

			if(reader != null)
			{
				while(reader.Read())
				{
					Core.Business.StaffAssess staffAssess = new Core.Business.StaffAssess();

					if (!reader.IsDBNull(0)) staffAssess.Id = reader.GetInt32(0);
					if (!reader.IsDBNull(1)) staffAssess.StaffCode = reader.GetString(1);
					if (!reader.IsDBNull(2)) staffAssess.StaffCate = reader.GetString(2);
					if (!reader.IsDBNull(3)) staffAssess.AssessCate = reader.GetString(3);
					if (!reader.IsDBNull(4)) staffAssess.AssessScore = reader.GetDecimal(4);

					staffAssess.MarkOld();
					staffAssesslist.Add(staffAssess);
				}
				reader.Close();
			}
			return staffAssesslist;
		}
		#endregion
	}
}
